package com.wanmait.travel.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.travel.util.DbHelper;
import com.wanmait.travel.vo.City;
import com.wanmait.travel.vo.Comment;
import com.wanmait.travel.vo.Manager;
import com.wanmait.travel.vo.Province;
import com.wanmait.travel.vo.Scenic;
import com.wanmait.travel.vo.UserPay;
import com.wanmait.travel.vo.Userinfo;

public class ManagerDAO 
{
	private static ManagerDAO ManagerDAO;
	private ManagerDAO() {}
	public  static ManagerDAO getDAO() {
		if(ManagerDAO==null) {
		 ManagerDAO = new ManagerDAO();
		}
		return ManagerDAO;
	}

	public void insert(Manager manager) 
	{
		String sql="insert into manager(name,pass,realname)values(?,?,?)";
		ArrayList params =new ArrayList<>();
		params.add(manager.getName());
		params.add(manager.getPass());
		params.add(manager.getRealName());
		
		DbHelper.update(sql, params);
	}
	public Manager findByNameAndPass(String userName, String userPass) {
		Manager manager=null;
		
		String sql="SELECT* FROM manager WHERE name=?AND pass=?";
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs =null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, userName);
			ps.setString(2, userPass);
			rs=ps.executeQuery();
			if(rs.next()) {
				manager=new Manager();
				manager.setId(rs.getInt("id"));
				manager.setName(userName);
				manager.setPass(userPass);
				manager.setRealName(rs.getString("real_name"));
			}
			

		} catch (SQLException e) {
		

			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return manager;
	}
	
	
	public List<Scenic> scenicCount() {
		String sql="SELECT scenic.* FROM scenic";
		List<Scenic> scenicName=new ArrayList<Scenic>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		
			try {
				ps=conn.prepareStatement(sql);
				rs=ps.executeQuery();
				while(rs.next())
				{
				Scenic scenic =new Scenic();
				scenic.setId(rs.getInt("id"));
				scenicName.add(scenic);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				DbHelper.close(rs, ps, conn);
			}
			return scenicName;
	}
	
	
	
	public List scenicAllComment()
	{
		List<Scenic> scenicCount=this.scenicCount();
		List scenicAllComment =new ArrayList();
		for (int i = 0; i < scenicCount.size(); i++) {
				List<Comment> scenicAC=new ArrayList<Comment>();
				String sql="SELECT scenic.*,COMMENT.*,COMMENT.id AS comment_id,manager.NAME AS manager_name  FROM scenic LEFT JOIN COMMENT ON  COMMENT.scenic_id=scenic.id LEFT JOIN manager ON scenic.manager_id=manager.id WHERE scenic.id=?";
				Connection conn=DbHelper.getConn();
				PreparedStatement ps=null;
				ResultSet rs=null;
				try {
					ps=conn.prepareStatement(sql);
					ps.setObject(1, scenicCount.get(i).getId());
					rs=ps.executeQuery();
					while(rs.next())
					{
						Comment comment=new Comment();
						comment.setId(rs.getInt("comment_id"));	
						comment.setScenicId(rs.getInt("id"));
						comment.setUserId(rs.getInt("userinfo_id"));
						comment.setComment(rs.getString("comment"));
						comment.setCommmentDate(rs.getTimestamp("comment_date"));
						Userinfo userinfo =new Userinfo();
						userinfo.setUsername(rs.getString("userinfo_id"));
						comment.setUserinfo(userinfo);
						Scenic scenic=new Scenic();
						scenic.setName(rs.getString("name"));
						comment.setScenic(scenic);
						Manager manager=new Manager();
						manager.setName(rs.getString("manager_name"));
						manager.setId(rs.getInt("manager_id"));
						comment.setManager(manager);
						Integer commentNum=this.commentNum(rs.getInt("id"));
						comment.setCommentCount(commentNum);
						scenicAC.add(comment);
						
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally {
					DbHelper.close(rs, ps, conn);
				}
				scenicAllComment.add(scenicAC);
			
		}
		return scenicAllComment;
	}
	
	
	public Integer commentNum(Integer scenicId)
	{
		String sql="SELECT scenic.*,COMMENT.*  FROM scenic LEFT JOIN  comment ON scenic.id=COMMENT.scenic_id WHERE scenic.id=?";
		
			int num=0;
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setObject(1, scenicId);
			rs=ps.executeQuery();
			while(rs.next())
			{
				if(rs.getString("comment")!=null) {
					num++;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return num;
	}
	
	public List<Scenic> managerFindAll(Integer managerId)
	{
		String sql="select scenic.*,manager.NAME AS manager_name,province.NAME AS province_name,city.NAME AS city_name from scenic LEFT JOIN province ON scenic.province_id=province.id LEFT JOIN city ON scenic.city_id=city.id LEFT JOIN manager on manager.id=scenic.manager_id where manager.id=?";
		List<Scenic> scenics=new ArrayList<Scenic>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setObject(1,managerId);
			rs=ps.executeQuery();
					while(rs.next()) {
						Scenic scenic=new Scenic();
						scenic.setId(rs.getInt("id"));
						scenic.setAnding(rs.getInt("anding"));
						scenic.setBad(rs.getInt("bad"));
						scenic.setCityId(rs.getInt("city_id"));
						scenic.setProvinceId(rs.getInt("province_id"));
						scenic.setCloseTime(rs.getString("close_time"));
						scenic.setGood(rs.getInt("good"));
						scenic.setIntro(rs.getString("intro"));
						scenic.setIntroPic(rs.getString("intro_pic"));
						scenic.setIntroVideo(rs.getString("intro_video"));
						scenic.setLatitude(rs.getDouble("latitude"));
						scenic.setLocation(rs.getString("location"));
						scenic.setOpenTime(rs.getString("open_time"));
						scenic.setTen(rs.getDouble("ten"));
						scenic.setName(rs.getString("name"));
						scenic.setEnable(rs.getBoolean("enable"));
						scenic.setManagerId(rs.getInt("manager_id"));
						Manager manager=new Manager();
						manager.setName(rs.getString("manager_name"));
						manager.setId(rs.getInt("manager_id"));
						scenic.setManager(manager);
						
						Province province =new Province();
						province.setName(rs.getString("province_name"));
						scenic.setProvince(province);
						
						City city=new City();
						city.setName(rs.getString("city_name"));
						scenic.setCity(city);
						
						scenics.add(scenic);
						
					}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return scenics;
	}
	
	public List<Comment> scenicCommentShow(Integer scenicId)
	{
		String sql="SELECT scenic.*,COMMENT.*,COMMENT.id AS comment_id,userinfo.username,manager.NAME AS manager_name  FROM scenic LEFT JOIN COMMENT ON  COMMENT.scenic_id=scenic.id LEFT JOIN manager ON scenic.manager_id=manager.id LEFT JOIN userinfo ON userinfo.id=COMMENT.userinfo_id WHERE scenic.id=?";
		List<Comment> comments=new ArrayList<Comment>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setObject(1, scenicId);
			rs=ps.executeQuery();
			while(rs.next())
			{
				Comment comment=new Comment();
				comment.setId(rs.getInt("comment_id"));	
				comment.setScenicId(rs.getInt("id"));
				comment.setUserId(rs.getInt("userinfo_id"));
				comment.setComment(rs.getString("comment"));
				comment.setCommmentDate(rs.getTimestamp("comment_date"));
				//用户增加
				Userinfo userinfo =new Userinfo();
				userinfo.setUsername(rs.getString("userinfo_id"));
				userinfo.setUsername(rs.getString("username"));
				comment.setUserinfo(userinfo);
				//景点增加
				Scenic scenic=new Scenic();
				scenic.setName(rs.getString("name"));
				comment.setScenic(scenic);
				//管理员增加
				Manager manager=new Manager();
				manager.setName(rs.getString("manager_name"));
				manager.setId(rs.getInt("manager_id"));
				comment.setManager(manager);
				
				Integer commentNum=this.commentNum(rs.getInt("id"));
				comment.setCommentCount(commentNum);
				comments.add(comment);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return comments;
	}
	
	public List<Comment> searchComment(String searchComment)
	{
		String sql="SELECT scenic.*,COMMENT.*,COMMENT.id AS comment_id,userinfo.*,manager.NAME AS manager_name  FROM scenic LEFT JOIN COMMENT ON  COMMENT.scenic_id=scenic.id LEFT JOIN manager ON scenic.manager_id=manager.id LEFT JOIN userinfo ON userinfo.id=COMMENT.userinfo_id  WHERE scenic.NAME LIKE ? OR manager.NAME LIKE ? OR userinfo.username LIKE ?";
		List<Comment> comments=new ArrayList<Comment>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setObject(1, "%"+searchComment+"%");
			ps.setObject(2, "%"+searchComment+"%");
			ps.setObject(3, "%"+searchComment+"%");
			rs=ps.executeQuery();
			while(rs.next())
			{
				Comment comment=new Comment();
				comment.setId(rs.getInt("comment_id"));	
				comment.setScenicId(rs.getInt("id"));
				comment.setUserId(rs.getInt("userinfo_id"));
				comment.setComment(rs.getString("comment"));
				comment.setCommmentDate(rs.getTimestamp("comment_date"));
				//用户增加
				Userinfo userinfo =new Userinfo();
				userinfo.setUsername(rs.getString("userinfo_id"));
				userinfo.setUsername(rs.getString("username"));
				comment.setUserinfo(userinfo);
				//景点增加
				Scenic scenic=new Scenic();
				scenic.setName(rs.getString("name"));
				comment.setScenic(scenic);
				//管理员增加
				Manager manager=new Manager();
				manager.setName(rs.getString("manager_name"));
				manager.setId(rs.getInt("manager_id"));
				comment.setManager(manager);
				
				Integer commentNum=this.commentNum(rs.getInt("id"));
				comment.setCommentCount(commentNum);
				comments.add(comment);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return comments;
	}
	
	public List<Comment> userComment(Integer userId)
	{
		String sql="select scenic.*,COMMENT.*,COMMENT.id AS comment_id,userinfo.*,manager.*,manager.NAME AS manager_name FROM scenic  LEFT JOIN manager ON manager.id=scenic.manager_id LEFT JOIN COMMENT ON COMMENT.scenic_id=scenic.id LEFT JOIN userinfo ON userinfo.id=COMMENT.userinfo_id WHERE userinfo.id=? ORDER BY scenic.id ";
		List<Comment> comments=new ArrayList<Comment>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setObject(1, userId);
			rs=ps.executeQuery();
			while(rs.next())
			{
				Comment comment=new Comment();
				comment.setId(rs.getInt("comment_id"));	
				comment.setScenicId(rs.getInt("id"));
				comment.setUserId(rs.getInt("userinfo_id"));
				comment.setComment(rs.getString("comment"));
				comment.setCommmentDate(rs.getTimestamp("comment_date"));
				//用户增加
				Userinfo userinfo =new Userinfo();
				userinfo.setUsername(rs.getString("userinfo_id"));
				userinfo.setUsername(rs.getString("username"));
				comment.setUserinfo(userinfo);
				//景点增加
				Scenic scenic=new Scenic();
				scenic.setName(rs.getString("name"));
				comment.setScenic(scenic);
				//管理员增加
				Manager manager=new Manager();
				manager.setName(rs.getString("manager_name"));
				manager.setId(rs.getInt("manager_id"));
				manager.setRealName(rs.getString("real_name"));;
				comment.setManager(manager);
				
				Integer commentNum=this.commentNum(rs.getInt("id"));
				comment.setCommentCount(commentNum);
				comments.add(comment);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return comments;
	}
		
	
	public List<UserPay> userPay(Integer userId)
	{
		String sql="SELECT pay.*,scenic.*,userinfo.* , tickit_type.NAME AS tickit_type_name,tickit.price AS tickit_price FROM pay LEFT JOIN scenic ON pay.scenic_id=scenic.id LEFT JOIN userinfo ON pay.user_id=userinfo.id LEFT JOIN tickit ON tickit.tickit_type_id=tickit.id LEFT  JOIN tickit_type ON pay.tickit_type_id=tickit_type.id WHERE userinfo.id=? ORDER BY scenic.id";
		List<UserPay> userPays=new ArrayList<UserPay>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setObject(1, userId);
			rs=ps.executeQuery();
			while(rs.next())
			{
				UserPay pay=new UserPay();
				pay.setUserId(rs.getInt("user_id"));
				pay.setScenicId(rs.getInt("scenic_id"));
				pay.setNickname(rs.getString("nickname"));
				pay.setNum(rs.getInt("num"));
				pay.setPayTime(rs.getTimestamp("pay_date"));
				pay.setPrice(rs.getDouble("tickit_price"));
				pay.setScenicName(rs.getString("name"));
				pay.setTickitTypeName(rs.getString("tickit_type_name"));
				userPays.add(pay);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return userPays;
	}
}
